package com.sc.web.gwt.server.persistence;


import com.sc.web.gwt.shared.model.Authenticator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class AuthenticatorPersistence extends AbstractDBConnection {

    public boolean insertAuthenticator(Authenticator authenticator) {
        String query = "INSERT INTO authenticators (Seed, SecretKey, Event)\n" +
                "VALUES (DES_ENCRYPT(?),DES_ENCRYPT(?),DES_ENCRYPT(?))";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, authenticator.getSeed());
            ps.setString(2, authenticator.getSecretKey());
            ps.setString(3, authenticator.getEvent());

            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public boolean updateAuthenticator(Authenticator authenticator) {
        String updateAuthenticatorQuery = "UPDATE authenticators\n" +
                "Set Seed = DES_ENCRYPT(?), SecretKey = DES_ENCRYPT(?), Event = DES_ENCRYPT(?)\n" +
                "WHERE AuthenticatorId = ?";
        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(updateAuthenticatorQuery);
            ps.setString(1, authenticator.getSeed());
            ps.setString(2, authenticator.getSecretKey());
            ps.setString(3, authenticator.getEvent());
            ps.setInt(4, authenticator.getAuthenticatorId());

            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + updateAuthenticatorQuery);
            e.printStackTrace();
            return false;
        }

        return true;
    }

    //Note: xem ham nay thay dc bang ham updateAuthenticator ko, neu co thi bo ham nay di dung ham updateAuthenticator thay vao
    public boolean changeSecretKey(String seed, String newPIN) {
        String changeAuthenticatorPINQuery = "Update authenticators\n" +
                "Set SecretKey = DES_ENCRYPT(?) \n" +
                "WHERE seed = DES_ENCRYPT(?)";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(changeAuthenticatorPINQuery);
            ps.setString(1, newPIN);
            ps.setString(2, seed);

            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + changeAuthenticatorPINQuery);
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public boolean syncAuthenticatorEvent(String seed, String event, String secretKey) {
        String syncAuthenticatorEventPINQuery = "Update authenticators \n" +
                "Set event = DES_ENCRYPT(?), secretKey = DES_ENCRYPT(?) \n" +
                "WHERE seed = DES_ENCRYPT(?)";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(syncAuthenticatorEventPINQuery);

            ps.setString(1, event);
            ps.setString(2, secretKey);
            ps.setString(3, seed);

            ps.execute();
            connection.close();
            return true;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + syncAuthenticatorEventPINQuery);
            e.printStackTrace();
        }

        return false;
    }

    public Authenticator getAuthenticatorBySeed(String seed) {
        String getAuthenticatorBySeedQuery = "SELECT AuthenticatorId, DES_DECRYPT(Seed), DES_DECRYPT(SecretKey), DES_DECRYPT(Event) FROM authenticators\n" +
                "WHERE Seed = DES_ENCRYPT(?)";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(getAuthenticatorBySeedQuery);
            ps.setString(1, seed);
            ResultSet resultSet = ps.executeQuery();
            resultSet.first();
            Authenticator authenticator = new Authenticator(resultSet.getInt(1),
                    resultSet.getString(2),
                    resultSet.getString(3),
                    resultSet.getString(4));
            connection.close();

            return authenticator;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + getAuthenticatorBySeedQuery);
            e.printStackTrace();
        }

        return null;
    }

    public List<Authenticator> getAllAuthenticators() {
        String query = "SELECT AuthenticatorId, DES_DECRYPT(Seed), DES_DECRYPT(SecretKey), DES_DECRYPT(Event) FROM authenticators";
        List<Authenticator> list = new ArrayList<Authenticator>();

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.next()) {
                list.add(new Authenticator(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4)));
                connection.close();
            }

            return list;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }

        return null;
    }

    public List<String> getAllSeed() {
        String getAllSeed = "SELECT DES_DECRYPT(Seed) FROM AUTHENTICATORS";

        List<String> seedList = new ArrayList<String>();

        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(getAllSeed);
            while (resultSet.next()) {
                seedList.add(resultSet.getString(1));
            }
            // clean
            resultSet.close();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + getAllSeed);
            e.printStackTrace();
        }
        return seedList;
    }

    public Authenticator getAuthenticatorByAccountCode(String accountCode) {
        String query = "SELECT authenticators.AuthenticatorId, DES_DECRYPT(Seed), DES_DECRYPT(SecretKey), DES_DECRYPT(Event) FROM authenticators INNER JOIN useraccounts ON authenticators.AuthenticatorId = useraccounts.AuthenticatorId\n" +
                "WHERE AccountCode = ?";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, accountCode);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.first()) {
                Authenticator authenticator = new Authenticator(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4));
                connection.close();

                return authenticator;
            }

            return null;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }

        return null;
    }
}